using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;

namespace OgilvyOne.SqlServerDAL
{
    public class Item : IItem, ITree
    {
        // Fields
        private const string PARM_ISDEL = "@IsDel";
        private const string PARM_ITEM_DESC = "@Item_Desc";
        private const string PARM_ITEM_ID = "@Item_ID";
        private const string PARM_ITEM_NAME = "@Item_Name";
        private const string PARM_ITEM_ORDER = "@Item_Order";
        private const string PARM_ITEM_VALUE = "@Item_Value";
        private const string PARM_ITEM_VALUE1 = "@Item_Value1";
        private const string PARM_PARENT_ITEM_ID = "@Parent_Item_ID";
        private const string SQL_DELETE_ITEM = "UPDATE item_tbl SET ISDEL = @IsDel WHERE Item_ID = @Item_ID";
        private const string SQL_INSERT_ITEM = "INSERT INTO item_tbl VALUES(@Parent_Item_ID,@Item_Name,@Item_Desc,@Item_Value,@Item_Value1,'0',@Item_Order)";
        private const string SQL_SELECT_ITEM = "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel,Item_Order FROM item_tbl  where isdel=@IsDel and item_id=@Item_ID";
        private const string SQL_SELECT_ITEM_CHILDLIST = "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel,Item_Order FROM item_tbl  where isdel=@IsDel and Parent_Item_ID=@Parent_Item_ID order by Item_Order asc,item_id asc";
        private const string SQL_SELECT_ITEM_INFO = "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,Item_Value,Item_Value1,IsDel,Item_Order FROM item_tbl  where isdel=@IsDel and item_id=@Item_ID";
        private const string SQL_SELECT_ITEM_LIST = "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel,Item_Order FROM item_tbl where isdel=@IsDel order by Item_Order asc,item_id asc";
        private const string SQL_UPDATE_ITEM = "UPDATE item_tbl SET Parent_Item_ID = @Parent_Item_ID, Item_Name = @Item_Name, Item_Desc = @Item_Desc,Item_Value=@Item_Value,Item_Value1=@Item_Value1,Item_Order=@Item_Order WHERE Item_ID = @Item_ID";

        // Methods
        public string ConvertItem(int item_id)
        {
            string str = "";
            if (item_id != 0)
            {
                IEnumerator enumerator = this.getCurrNode(item_id).GetEnumerator();
                if (enumerator.MoveNext())
                {
                    str = ((TreeInfo)enumerator.Current).Node_Name;
                }
            }
            return str;
        }

        public string ConvertItem(string item_id)
        {
            string str = "";
            if (item_id == "")
            {
                item_id = "0";
            }
            if (item_id != "0")
            {
                IEnumerator enumerator = this.getCurrNode(int.Parse(item_id)).GetEnumerator();
                if (enumerator.MoveNext())
                {
                    str = ((TreeInfo)enumerator.Current).Node_Desc;
                }
            }
            return str;
        }

        public void DeleteNode(int node_id)
        {
            SqlParameter[] itemDeleteParameters = GetItemDeleteParameters();
            itemDeleteParameters[0].Value = node_id;
            itemDeleteParameters[1].Value = "1";
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "UPDATE item_tbl SET ISDEL = @IsDel WHERE Item_ID = @Item_ID", itemDeleteParameters);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public IList getChildNodeList(string isdel, int parent_node_id)
        {
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Parent_Item_ID", SqlDbType.Int, 4, "Parent_Item_ID"), new SqlParameter("@IsDel", SqlDbType.Char, 1, "IsDel") };
            cmdParms[0].Value = parent_node_id;
            cmdParms[1].Value = isdel;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel,Item_Order FROM item_tbl  where isdel=@IsDel and Parent_Item_ID=@Parent_Item_ID order by Item_Order asc,item_id asc", cmdParms))
            {
                while (reader.Read())
                {
                    TreeInfo info = new TreeInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4));
                    list.Add(info);
                }
            }
            return list;
        }

        public ItemInfo getCurrItemInfo(int itemid)
        {
            ItemInfo info = null;
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Item_ID", SqlDbType.Int, 4, "Item_ID"), new SqlParameter("@IsDel", SqlDbType.Char, 1, "IsDel") };
            cmdParms[0].Value = itemid;
            cmdParms[1].Value = "0";
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,Item_Value,Item_Value1,IsDel,Item_Order FROM item_tbl  where isdel=@IsDel and item_id=@Item_ID", cmdParms))
            {
                while (reader.Read())
                {
                    info = new ItemInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5));
                    info.Item_Order = reader.GetInt32(7);
                }
            }
            return info;
        }

        public IList getCurrNode(int nodeid)
        {
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Item_ID", SqlDbType.Int, 4, "Item_ID"), new SqlParameter("@IsDel", SqlDbType.Char, 1, "IsDel") };
            cmdParms[0].Value = nodeid;
            cmdParms[1].Value = "0";
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel,Item_Order FROM item_tbl  where isdel=@IsDel and item_id=@Item_ID", cmdParms))
            {
                while (reader.Read())
                {
                    TreeInfo info = new TreeInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4));
                    list.Add(info);
                }
            }
            return list;
        }

        public TreeInfo getCurrTreeInfo(int nodeid)
        {
            TreeInfo info = new TreeInfo();
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Item_ID", SqlDbType.Int, 4, "Item_ID"), new SqlParameter("@IsDel", SqlDbType.Char, 1, "IsDel") };
            cmdParms[0].Value = nodeid;
            cmdParms[1].Value = "0";
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel,Item_Order FROM item_tbl  where isdel=@IsDel and item_id=@Item_ID", cmdParms))
            {
                while (reader.Read())
                {
                    info = new TreeInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4));
                }
            }
            return info;
        }

        private static SqlParameter[] GetItemDeleteParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("UPDATE item_tbl SET ISDEL = @IsDel WHERE Item_ID = @Item_ID");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Item_ID", SqlDbType.Int, 4), new SqlParameter("@IsDel", SqlDbType.Char, 1) };
                SQLHelper.CacheParameters("UPDATE item_tbl SET ISDEL = @IsDel WHERE Item_ID = @Item_ID", cachedParameters);
            }
            return cachedParameters;
        }

        public int getItemIDByDesc(string item_desc, int parent_item_id)
        {
            int num = 1;
            string cmdText = string.Concat(new object[] { "select item_id from item_tbl where item_desc='", item_desc, "' and parent_item_id=", parent_item_id });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    num = reader.GetInt32(0);
                }
            }
            return num;
        }

        public IList getItemList(int parent_item_id)
        {
            IList list = new ArrayList();
            string cmdText = "select Item_ID,Parent_Item_ID,Item_Name,Item_Desc,Item_Value,Item_Value1,Item_Order from item_tbl where parent_item_id=" + parent_item_id + " and isdel='0' order by item_order";
            SqlParameter[] cmdParms = new SqlParameter[0];
            ItemInfo info = null;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    info = new ItemInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5));
                    info.Item_Order = reader.GetInt32(6);
                    list.Add(info);
                }
            }
            return list;
        }

        public string getItemNameByItemId(int item_desc, int parent_item_id)
        {
            string str = string.Empty;
            string cmdText = string.Concat(new object[] { "select item_name from item_tbl where item_desc='", item_desc, "' and parent_item_id=", parent_item_id });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    str = reader.GetString(0);
                }
            }
            return str;
        }

        private static SqlParameter[] GetItemParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("INSERT INTO item_tbl VALUES(@Parent_Item_ID,@Item_Name,@Item_Desc,@Item_Value,@Item_Value1,'0',@Item_Order)");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Parent_Item_ID", SqlDbType.Int, 4), new SqlParameter("@Item_Name", SqlDbType.VarChar, 100), new SqlParameter("@Item_Desc", SqlDbType.VarChar, 100), new SqlParameter("@Item_Value", SqlDbType.VarChar, 100), new SqlParameter("@Item_Value1", SqlDbType.VarChar, 100), new SqlParameter("@Item_Order", SqlDbType.Int, 4) };
                SQLHelper.CacheParameters("INSERT INTO item_tbl VALUES(@Parent_Item_ID,@Item_Name,@Item_Desc,@Item_Value,@Item_Value1,'0',@Item_Order)", cachedParameters);
            }
            return cachedParameters;
        }

        private static SqlParameter[] GetItemParametersID()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("UPDATE item_tbl SET Parent_Item_ID = @Parent_Item_ID, Item_Name = @Item_Name, Item_Desc = @Item_Desc,Item_Value=@Item_Value,Item_Value1=@Item_Value1,Item_Order=@Item_Order WHERE Item_ID = @Item_ID");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Item_ID", SqlDbType.Int, 4), new SqlParameter("@Parent_Item_ID", SqlDbType.Int, 4), new SqlParameter("@Item_Name", SqlDbType.VarChar, 100), new SqlParameter("@Item_Desc", SqlDbType.VarChar, 100), new SqlParameter("@Item_Value", SqlDbType.VarChar, 100), new SqlParameter("@Item_Value1", SqlDbType.VarChar, 100), new SqlParameter("@Item_Order", SqlDbType.Int, 4) };
                SQLHelper.CacheParameters("UPDATE item_tbl SET Parent_Item_ID = @Parent_Item_ID, Item_Name = @Item_Name, Item_Desc = @Item_Desc,Item_Value=@Item_Value,Item_Value1=@Item_Value1,Item_Order=@Item_Order WHERE Item_ID = @Item_ID", cachedParameters);
            }
            return cachedParameters;
        }

        public IList getRoleChildNodeList(string isdel, int parent_node_id)
        {
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Parent_Item_ID", SqlDbType.Int, 4, "Parent_Item_ID"), new SqlParameter("@IsDel", SqlDbType.Char, 1, "IsDel") };
            cmdParms[0].Value = parent_node_id;
            cmdParms[1].Value = isdel;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel,Item_Order FROM item_tbl  where isdel=@IsDel and Parent_Item_ID=@Parent_Item_ID order by Item_Order asc,item_id asc", cmdParms))
            {
                while (reader.Read())
                {
                    TreeInfo info = new TreeInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4));
                    list.Add(info);
                }
            }
            return list;
        }

        public void InsertNode(ItemInfo acc)
        {
            SqlParameter[] itemParameters = GetItemParameters();
            this.SetItemParameters(itemParameters, acc);
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        Console.WriteLine("INSERT INTO item_tbl VALUES(@Parent_Item_ID,@Item_Name,@Item_Desc,@Item_Value,@Item_Value1,'0',@Item_Order)");
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "INSERT INTO item_tbl VALUES(@Parent_Item_ID,@Item_Name,@Item_Desc,@Item_Value,@Item_Value1,'0',@Item_Order)", itemParameters);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void ModifyNode(ItemInfo myItem)
        {
            SqlParameter[] itemParametersID = GetItemParametersID();
            this.SetItemParametersID(itemParametersID, myItem);
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "UPDATE item_tbl SET Parent_Item_ID = @Parent_Item_ID, Item_Name = @Item_Name, Item_Desc = @Item_Desc,Item_Value=@Item_Value,Item_Value1=@Item_Value1,Item_Order=@Item_Order WHERE Item_ID = @Item_ID", itemParametersID);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        private void SetItemParameters(SqlParameter[] parms, ItemInfo acc)
        {
            parms[0].Value = acc.Parent_Node_ID;
            parms[1].Value = acc.Node_Name;
            parms[2].Value = acc.Node_Desc;
            parms[3].Value = acc.Item_Value;
            parms[4].Value = acc.Item_Value1;
            parms[5].Value = acc.Item_Order;
        }

        private void SetItemParametersID(SqlParameter[] parms, ItemInfo acc)
        {
            parms[0].Value = acc.Node_ID;
            parms[1].Value = acc.Parent_Node_ID;
            parms[2].Value = acc.Node_Name;
            parms[3].Value = acc.Node_Desc;
            parms[4].Value = acc.Item_Value;
            parms[5].Value = acc.Item_Value1;
            parms[6].Value = acc.Item_Order;
        }
    }
}
